IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_GenericDelete')
          AND TYPE IN ('P','PC'))
   DROP PROCEDURE USP_GenericDelete
GO

/****** Object:  StoredProcedure [dbo].[USP_GenericDelete]    Script Date: 10/22/2010 13:34:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Boonyarit
-- Create date: 15/11/2553
-- =============================================
CREATE PROCEDURE [dbo].[USP_GenericDelete]
	-- Add the parameters for the stored procedure here
	@tbl nvarchar(max), -- Table's name to delete
	@pid nvarchar(max), -- Primary key's table to delete
	@cid nvarchar(max)  -- Series of identities to delete
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	-- ==========================================
	-- Data for testing
	-- ==========================================
	--set		@tbl = 'dummy2'
	--set		@pid = 'id'
	--set		@cid = '1,2,3'

	-- ==========================================
	-- Validate data before deleting
	-- ==========================================
	declare @tbl_ids table (position int,value int)
	insert	into @tbl_ids (position,value)
	select	position,value from dbo.UFN_Split(@cid,',') as t
	
	
	declare @loop_id int
	select	@loop_id = count(*)
	from	@tbl_ids
	
	while (@loop_id > 0) begin
	
		declare @loop_tid int
		select	@loop_tid = value
		from	@tbl_ids
		where	position = @loop_id
	
		-- ========================================
		-- This section for calling validate SP
		-- ========================================
		if (@tbl = 'master_department')
			exec dbo.USP_Validate_Delete_master_department @loop_tid
		else if (@tbl = 'master_type')
			exec dbo.USP_Validate_Delete_master_type @loop_tid
		else if (@tbl = 'stp_main_system')
			exec dbo.USP_Validate_Delete_stp_main_system @loop_tid
		else if (@tbl = 'stp_auth_department')
			exec dbo.USP_Validate_Delete_stp_auth_department @loop_tid
	    else if (@tbl = 'group_user')
			exec dbo.USP_Validate_Delete_group_user @loop_tid
	    --else if (@tbl = 'staff')
		--	exec dbo.USP_Validate_Delete_staff @loop_tid
		else if (@tbl = 'stp_menu_system')
			exec dbo.USP_Validate_Delete_stp_menu_system @loop_tid	
			
		set @loop_id = @loop_id - 1;
	end

	-- ==========================================
	-- Dynamic SQL for delete
	-- ==========================================
	declare @statement nvarchar(max)
	declare @NumTablesDeletedFrom int
	set		@statement = N'delete from ' + @tbl + ' where ' + @pid + ' in (' + @cid+ ' )';


	-- ==========================================
	-- Excecute Dynamic SQL
	-- ==========================================
	-- =============E_X_E_C_E_C_U_T_E  S_Q_L=====================
	exec sp_executesql @statement;
	
END


GO


